import config
import loger
import sqlite3
import pathlib
from decimal import Decimal
from config import log
from config import runsql,runsqlmany

root=pathlib.Path(__file__).parent
db=sqlite3.connect(str(root/"statistics.db"))
cur = db.cursor()


def creatTable(table,columns):

    try :
        cur.execute(f"DROP TABLE IF EXISTS `{table}`;")
        db.commit()
        log.info(f"删除表{table}成功")
    except Exception as e:
        db.rollback()
        log.warning(f"删除表{table}失败：{e}")
    columns_str = "\" real , \"".join(columns+["总分"])
    sql=f"CREATE TABLE IF NOT EXISTS \"{table}\" ( \"学籍号\" INTEGER NOT NULL, \"{columns_str}\" real, PRIMARY KEY (\"学籍号\") );"
    log.debug(sql)
    try:
        cur.execute(sql)
        db.commit()
        log.info(f"创建{table}表成功")
    except Exception as e:
        log.error(f"创建{table}表失败: {e}")

def subjectCalculation():  #成绩折算
    for i in config.subjectList:
        if(i in config.SubjectMerger.keys()):
            columns_str="` , `".join(config.SubjectMerger[i])
        else:
            columns_str=i

        for g in config.subject:
            sql=f"select `学籍号`,`{columns_str}` from `source` where `年级` = '{g}' ;"
            result=cur.execute(sql)
            result=result.fetchall()
            datatemp=[]
            for j in result:
                v2_=j[2] if(len(j)==3) else None
                status_=config.DataConversion(i,g,j[1],v2_)
                if(status_[0]):
                    temp2 = None if status_[1] is None else float(status_[1])
                    datatemp.append((temp2,j[0]))
                    temp2=None
                else:
                    log.error(f"学籍号{j[0]}的{i}成绩{status_[1]}")
                    config._exit("发生错误",15)
            try:
                cur.executemany(f"INSERT OR IGNORE INTO result_1 (`{i}`,`学籍号`) VALUES (?, ?) ;",datatemp)
                cur.executemany(f"UPDATE result_1 SET `{i}` = ? WHERE `学籍号` = ? ;" ,datatemp)
                db.commit()
                log.info(f"{g}{i}成绩折算完成")
            except Exception as e:
                log.error(f"插入数据出错: {e}")
                db.rollback()
                config._exit("发生错误",15)
    # 总分计算
    for i in config.subject:
        sql=f"select `学校` from source where `年级`='{i}' group by `学校`;"
        result=runsql(sql)
        for j in result.fetchall():
            columns_str="`,`".join(config.subject[i])
            sql=f"select `学籍号`,`{columns_str}` from convertedScore where `年级`='{i}' and `学校`='{j[0]}' ;"
            result2=runsql(sql)
            datatemp=[]
            for n in result2:
                sumr=Decimal('0')
                for k in n[1:] :
                    if (k is None): k=Decimal(0)
                    if( k<0 ): 
                        log.info(f"{n[0]} 成绩标记为 {k}") 
                        k=Decimal(0) 
                    sumr+=Decimal(str(k))

                if(float(sumr)==0 ):
                    sumr=config.MissingExamination
                else:
                    sumr=float(sumr)

                datatemp.append((sumr,n[0]))
            sql=f"UPDATE result_1 SET `总分` = ?  WHERE `学籍号` = ? ;"

            if(not runsqlmany(sql,datatemp)):
                config._exit("发生错误",15)
            else:
                log.info(f"{i}{j[0]}总分计算完成")


            

def creatView():
    try:
        cur.execute(f"DROP VIEW \"convertedScore\";")
        db.commit()
        log.info("视图删除成功")
    except Exception as e:
        log.warning(f"视图删除失败 {e}")
        db.rollback()
        
    columns_str=""
    for i in config.subjectList+["总分"]:
        columns_str += f",result_1.\"{i}\""
    sql=f"CREATE VIEW \"convertedScore\" AS SELECT source.\"学籍号\",source.\"区域\",source.\"学校\",source.\"年级\",source.\"班级\",source.\"姓名\" {columns_str} FROM source LEFT JOIN result_1 ON source.\"学籍号\" = result_1.\"学籍号\" ;"
    try:
        cur.execute(sql)
        db.commit()
        log.info("视图创建成功")
    except Exception as e:
        log.warning(f"视图创建失败 {e}")
        db.rollback()     

if __name__ =="__main__":
    creatTable("result_1",config.subjectList)
    creatView()
    subjectCalculation()
    config._exit("已完成折算",3)

    

